1. Field of the Invention
The present invention generally relates to SQL statements. Specifically, the present invention relates to rewriting view statements in SQL statements to provide increased performance and speed.
2. Related Art
Structured query language (SQL) is a standard interactive and programming language for getting information to and from a database. Although SQL is both an ANSI and an ISO standard, many database products support SQL with proprietary extensions to the standard language. Queries take the form of a command language that allows a user to select, insert, update and find the location of data.
For example, a simple SQL query may request the rows of a table where a customer purchase order column entry is “123” and an order number is “456.” The problem, however, is as follows. Some types of SQL statements contain “full select” statements that reference one or more “views.” A “full select” statement includes the “where” (i.e., predicate) part of the query, where the criteria for the search are defined. A “view” is a resultant table that results from an operation(s) performed on other tables specified in the definition of the view. Thus, the “view” specifies the operation and the tables upon which the operation is performed. For example, one table may list all customer purchase order entries whenever ordered, whereas the “view” may specify only those customer purchase order entries from this table made after the year 2000. As such, the view is a subset of the table that lists only those rows from the table where the customer purchase order entries were made after the year 2000.
In some instances, a “view” can be more complicated, such as by containing a Left Outer Join (LOJ) operation. For example, assume that there is a customer ID table and an order table. A LOJ of these two tables will yield a third table with a row for each customer with an order, and a row for each customer without an order. It has been found that when the “full select” portion of the SQL statement includes a reference to a view with an LOJ operation, the search takes a long period of time to complete. Heretofore, no existing solution has been proposed for addressing such latency.
In view of the foregoing, there exists a need for a method, system and program product for rewriting view statements in SQL statements. Specifically, a need exists for a system that can modify a SQL statement such that the “full select” portion will not contain any reference to view containing a LOJ operation.